﻿using ERPU8WebApi.Until;
using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Text;
using System.Web;

namespace ERPU8WebApi.Dal
{
    public class DALBom
    {
        SqlHelper s = new SqlHelper();
        public int GetPartId(string cinvCode)
        {
            int ccode = 0;
            string sql = string.Format(@"SELECT partid FROM bas_part WHERE InvCode='{0}' ", cinvCode);
            DataTable dt = new DataTable();
            dt = s.ExecuteDataSetSql(sql, 0).Tables[0];
            if (dt.Rows.Count > 0)
            {
                ccode = int.Parse(dt.Rows[0]["partid"].ToString());
            }
            return ccode;
        }
        //获取bom最大版本号
        public string GetMaxBOMVersion(string cinvcode)
        {
            string sql = string.Format(@"SELECT MAX(b.Version)as version FROM bom_bom b where BomId IN 
                                        (SELECT BomId from bom_parent where ParentId 
                                         IN (SELECT PartId FROM bas_part where InvCode='{0}'))", cinvcode);
            DataTable dt = new DataTable();
            dt = s.ExecuteDataSetSql(sql, 0).Tables[0];
            if (dt.Rows.Count > 0)
            {
                if (!string.IsNullOrEmpty(dt.Rows[0]["version"].ToString()))
                {
                    return dt.Rows[0]["version"].ToString();
                }
            }
            return "1";
        }
        //获取bom最大版本号的bom类型
        public int GetMaxBOMType(string cinvcode,string bomMaxVersion)
        {
            string sql = string.Format(@"SELECT b.BomType FROM bom_bom b where BomId IN 
                                        (SELECT BomId from bom_parent where ParentId IN 
                                        (SELECT PartId FROM bas_part where InvCode='{0}')) 
                                        AND b.Version ={1}", cinvcode, bomMaxVersion);
            DataTable dt = new DataTable();
            dt = s.ExecuteDataSetSql(sql, 0).Tables[0];
            if (dt.Rows.Count > 0)
            {
                if (!string.IsNullOrEmpty(dt.Rows[0]["BomType"].ToString()))
                {
                    return int.Parse( dt.Rows[0]["BomType"].ToString());
                }
            }
            return 1;
        }

        /// <summary>
        /// 获得ERP的BOM信息
        /// </summary>
        /// <param name="CinvCode"></param>
        /// <returns></returns>
        public DataTable GetErpNewBom(string CinvCode, string CinvStd)
        {
            DataTable _Dt = new DataTable();
            StringBuilder _Strb = new StringBuilder();
            _Strb.Append("select i.cInvCode,i.cInvStd,i.cInvName,c.cComUnitName,");
            _Strb.Append("v.EffBegDate,v.EffEndDate,(v.BaseQtyN/v.BaseQtyD) as UnitUseCount,p.Version,p.VersionDesc ");
            _Strb.Append("from ( ");
            _Strb.Append("select t.InvCode,ROW_NUMBER() OVER(PARTITION BY t.InvCode ORDER BY b.Version DESC) as RIndex,b.* ");
            _Strb.Append("from bom_bom b ");
            _Strb.Append("inner join bom_parent p on b.BomId=p.BomId ");
            _Strb.Append("inner join bas_part t on p.ParentId=t.PartId ");
            _Strb.Append("where b.Status=3 ");
            //存货编码限制
            if (!string.IsNullOrEmpty(CinvCode))
            {
                _Strb.Append(" and ( ");
                for (int LoopIndex = 0; LoopIndex <= CinvCode.Split('_').Length - 1; LoopIndex++)
                {
                    if (LoopIndex == CinvCode.Split('_').Length - 1)
                    {
                        _Strb.Append(" t.InvCode ='" + CinvCode.Split('_')[LoopIndex] + "' ) ");
                    }
                    else
                    {
                        _Strb.Append(" t.InvCode='" + CinvCode.Split('_')[LoopIndex] + "' or ");
                    }
                }
            }
            _Strb.Append(") p ");
            _Strb.Append("inner join v_bom_opcomponent_rpt v on p.BomId=v.BomId ");
            _Strb.Append("inner join Inventory i on v.InvCode=i.cInvCode ");
            _Strb.Append("inner join ComputationUnit c on i.cComUnitCode=c.cComunitCode ");
            _Strb.Append("where p.RIndex=1 ");
            //规格型号限制
            if (!string.IsNullOrEmpty(CinvStd))
            {
                _Strb.Append(" and ( ");
                for (int LoopIndex = 0; LoopIndex <= CinvStd.Split('_').Length - 1; LoopIndex++)
                {
                    if (LoopIndex == CinvStd.Split('_').Length - 1)
                    {
                        _Strb.Append(" i.cInvStd ='" + CinvStd.Split('_')[LoopIndex] + "' ) ");
                    }
                    else
                    {
                        _Strb.Append(" i.cInvStd='" + CinvStd.Split('_')[LoopIndex] + "' or ");
                    }
                }
            }
            DataTable dt = new DataTable();
            dt = s.ExecuteDataSetSql(_Strb.ToString(), 0).Tables[0];
            return dt;
        }


        public DataTable GetAllBom(string cinvCode)
        {
            string sql = string.Format(@"declare @ID varchar(50) set @ID ='{0}' 
;with t as
 (
select * from (
SELECT dbo.v_bom_parent_rpt.InvCode AS mjbm, b.cInvName AS mjmc, b.cInvStd AS mjgg, dbo.v_bom_parent_rpt.cFree2 AS gx1,
dbo.v_bom_opcomponent_rpt.BaseQtyN AS jbyl, dbo.v_bom_opcomponent_rpt.BaseQtyD AS jcyl, dbo.v_bom_opcomponent_rpt.InvCode AS zjbm,
a.cInvName AS zjmc, a.cInvStd AS zjgg, dbo.v_bom_opcomponent_rpt.cFree2 AS gx2
FROM dbo.v_bom_parent_rpt INNER JOIN
dbo.v_bom_opcomponent_rpt ON dbo.v_bom_parent_rpt.BomId = dbo.v_bom_opcomponent_rpt.BomId INNER JOIN
dbo.Inventory AS a ON dbo.v_bom_opcomponent_rpt.InvCode = a.cInvCode INNER JOIN
dbo.Inventory AS b ON dbo.v_bom_parent_rpt.InvCode = b.cInvCode
INNER JOIN bom_bom bo ON bo.BomId=v_bom_opcomponent_rpt.BomId
WHERE bo.VersionEndDate>GETDATE()
) as ssk_wlqd
WHERE MJBM=@id
union all
SELECT B1.* from (
SELECT dbo.v_bom_parent_rpt.InvCode AS mjbm, b.cInvName AS mjmc, b.cInvStd AS mjgg, dbo.v_bom_parent_rpt.cFree2 AS gx1,
dbo.v_bom_opcomponent_rpt.BaseQtyN AS jbyl, dbo.v_bom_opcomponent_rpt.BaseQtyD AS jcyl, dbo.v_bom_opcomponent_rpt.InvCode AS zjbm,
a.cInvName AS zjmc, a.cInvStd AS zjgg, dbo.v_bom_opcomponent_rpt.cFree2 AS gx2
FROM dbo.v_bom_parent_rpt 
INNER JOIN
dbo.v_bom_opcomponent_rpt ON dbo.v_bom_parent_rpt.BomId = dbo.v_bom_opcomponent_rpt.BomId 
INNER JOIN
dbo.Inventory AS a ON dbo.v_bom_opcomponent_rpt.InvCode = a.cInvCode 
INNER JOIN
dbo.Inventory AS b ON dbo.v_bom_parent_rpt.InvCode = b.cInvCode
INNER JOIN bom_bom bo ON bo.BomId=v_bom_opcomponent_rpt.BomId
WHERE bo.VersionEndDate>GETDATE()
) as B1

 join T AS B2 ON B1.mjbm=B2.zjbm and b1.gx1=b2.gx2
) SELECT * FROM T", cinvCode);
            DataTable dt = new DataTable();
            dt = s.ExecuteDataSetSql(sql, 0).Tables[0];
            return dt;
        }


    }
}